package uf.audit.util.poi;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.jfinal.plugin.activerecord.Record;

import uf.audit.util.StrKit;

public class POIExporter {
	
	private static final Logger log = Logger.getLogger(POIExporter.class);
	
	private static int createHeader(Sheet sheet, CellStyle defaultStyle, List<String> headerList, List<Integer> columnWidthList, int rowIndex){
		Row row = sheet.createRow(rowIndex++);
		for (int i = 0, length = headerList.size(); i < length; i++) {
			Cell cell = row.createCell(i);
			HSSFRichTextString text = new HSSFRichTextString(headerList.get(i));
			cell.setCellValue(text);
			cell.setCellStyle(defaultStyle);
			if(columnWidthList != null){
				sheet.setColumnWidth(i, columnWidthList.get(i));
			}
		}
		return rowIndex;
	}
	
	private static int createMultiHeader(Sheet sheet, CellStyle defaultStyle, List<List<Map<String, String>>> multiHeaderList, int rowIndex){
		for (int i = 0, length = multiHeaderList.size(); i < length; i++) {
			Row row = sheet.createRow(rowIndex);
			
			List<Map<String, String>> rowList = multiHeaderList.get(i);
			for(int j = 0, jLen = rowList.size(); j < jLen; j++){
				Map<String, String> cellMap = rowList.get(j);
				
				Cell cell = row.createCell(j);
				cell.setCellStyle(defaultStyle);
				if(cellMap != null){
					HSSFRichTextString text = new HSSFRichTextString(cellMap.get("cap"));
					cell.setCellValue(text);
					
					int colSpan = (cellMap.get("col") != null && StrKit.isNumeric(cellMap.get("col"))) ? Integer.valueOf(cellMap.get("col")) : 1;
					int rowSpan = (cellMap.get("row") != null && StrKit.isNumeric(cellMap.get("row"))) ? Integer.valueOf(cellMap.get("row")) : 1;
					if(colSpan > 1 || rowSpan > 1){
						CellRangeAddress range = new CellRangeAddress(rowIndex, rowIndex + rowSpan - 1, j, j + colSpan - 1);
						sheet.addMergedRegion(range);
					}
				}
			}
			rowIndex++;
		}
		return rowIndex;
	}
	
	private static void creatDataRow(Sheet sheet, CellStyle defaultStyle, List<String> keyList, List<Record> dataset, int rowIndex){
		if(dataset != null && !dataset.isEmpty()){
			for (Record rec : dataset) {
				Row row = sheet.createRow(rowIndex++);
				for (int i = 0, length = keyList.size(); i < length; i++) {
					Object value = rec.get(keyList.get(i));
					String textValue;
					if (value instanceof Boolean) {
						boolean bValue = (Boolean) value;
						textValue = (!bValue) ? "否" : "是";
					} else if (value instanceof Date) {
						Date date = (Date) value;
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						textValue = sdf.format(date);
					} else {
						textValue = (value != null) ? value.toString() : "";
					}
					Cell cell = row.createCell(i);
					cell.setCellValue(textValue);
					cell.setCellStyle(defaultStyle);
				}
			}
		}
	}
	
	private static void creatSheet(Workbook workbook, String sheetName, SheetConfig sheetConfig, List<Record> dataset){
		List<String> headerList = sheetConfig.getHeaderList();
		List<String> keyList = sheetConfig.getColumnList();
		List<Integer> columnWidthList = sheetConfig.getColumnWidthList();
		
		Sheet sheet = workbook.createSheet(sheetName);
		
		CellStyle defaultStyle = createBorderedStyle(workbook);
		defaultStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
		defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		
		int rowIndex = 0;
		rowIndex = createHeader(sheet, defaultStyle, headerList, columnWidthList, rowIndex);
		defaultStyle = createBorderedStyle(workbook);
		creatDataRow(sheet, defaultStyle, keyList, dataset, rowIndex);
	}
	
	private static void writeWorkBook(Workbook workbook, OutputStream out) throws Exception{
		workbook.write(out);
	}
	
	private static void writeWorkBook(Workbook workbook, String fileNamePath) throws Exception{
		FileOutputStream outputStream = null;
		try {
			outputStream = new FileOutputStream(fileNamePath);
			workbook.write(outputStream);
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			try {
				if(outputStream != null) outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 公用导出excel方法
	 * @param sheetName    sheet页名称
	 * @param headerList  第一行表头（用于excel输出）
	 * @param keyList     第一行表头对应数据集合的key值
	 * @param dataset  数据集合
	 * @param out
	 * @return
	 */
	public static void exportExcel(String sheetName, List<String> headerList, List<String> keyList, List<Record> dataset, OutputStream out) {
		if (headerList == null || keyList == null || headerList.size() != keyList.size()){
			return;
		}
		Workbook workbook = new HSSFWorkbook();
		Sheet sheet = workbook.createSheet(sheetName);
		CellStyle defaultStyle = createBorderedStyle(workbook);
		defaultStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
		defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		
		int rowIndex = 0;
		try {
			rowIndex = createHeader(sheet, defaultStyle, headerList, null, rowIndex);
			defaultStyle = createBorderedStyle(workbook);
			creatDataRow(sheet, defaultStyle, keyList, dataset, rowIndex);
			writeWorkBook(workbook, out);
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
		}
	}
	
	/**
	 * 公用导出excel方法
	 * @param sheetName    sheet页名称
	 * @param headerList   第一行表头（用于excel输出）
	 * @param keyList      第一行表头对应数据集合的key值
	 * @param dataset      数据集合
	 * @param fileNamePath 文件输出路径
	 * @return
	 */
	public static void exportExcel(String sheetName, List<String> headerList, List<String> keyList, List<Record> dataset, String fileNamePath) {
		if (headerList == null || keyList == null || headerList.size() != keyList.size()){
			return;
		}
		try {
			Workbook workbook = new HSSFWorkbook();
			Sheet sheet = workbook.createSheet(sheetName);
			CellStyle defaultStyle = createBorderedStyle(workbook);
			defaultStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
			defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			
			int rowIndex = 0;
			rowIndex = createHeader(sheet, defaultStyle, headerList, null, rowIndex);
			defaultStyle = createBorderedStyle(workbook);
			creatDataRow(sheet, defaultStyle, keyList, dataset, rowIndex);
			
			writeWorkBook(workbook, fileNamePath);
		} catch (Exception e) {
			e.printStackTrace();
			//log.error(e.getMessage(), e);
		}
	}
	
	public static void exportMultiHeadExcel(String sheetName, List<List<Map<String, String>>> multiHeaderList, List<String> keyList, List<Record> dataset, String fileNamePath) {
		try {
			Workbook workbook = new HSSFWorkbook();
			Sheet sheet = workbook.createSheet(sheetName);
			CellStyle defaultStyle = createBorderedStyle(workbook);
			defaultStyle.setAlignment(CellStyle.ALIGN_CENTER_SELECTION);
			defaultStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
			
			int rowIndex = 0;
			rowIndex = createMultiHeader(sheet, defaultStyle, multiHeaderList, rowIndex);
			defaultStyle = createBorderedStyle(workbook);
			creatDataRow(sheet, defaultStyle, keyList, dataset, rowIndex);
			
			writeWorkBook(workbook, fileNamePath);
		} catch (Exception e) {
			e.printStackTrace();
			//log.error(e.getMessage(), e);
		}
	}
	
	public static void exportExcel(String sheetName, SheetConfig sheetConfig, List<Record> dataset, String fileNamePath) {
		try {
			Workbook workbook = new HSSFWorkbook();
			creatSheet(workbook, sheetName, sheetConfig, dataset);
			writeWorkBook(workbook, fileNamePath);
		} catch (Exception e) {
			e.printStackTrace();
			//log.error(e.getMessage(), e);
		}
	}
	
	public static void exportExcel(List<String> sheetNameList, List<SheetConfig> sheetConfigList, List<List<Record>> sheetData, String fileNamePath) {
		if (sheetNameList == null || sheetConfigList == null || sheetData == null || sheetNameList.size() != sheetConfigList.size() || sheetNameList.size() != sheetData.size()){
			return;
		}
		try {
			Workbook workbook = new HSSFWorkbook();
			for(int sheetIndex = 0, sheetNumber = sheetNameList.size(); sheetIndex < sheetNumber; sheetIndex++){
				String sheetName = sheetNameList.get(sheetIndex);
				SheetConfig sheetConfig = sheetConfigList.get(sheetIndex);
				List<Record> dataset = sheetData.get(sheetIndex);
				creatSheet(workbook, sheetName, sheetConfig, dataset);
			}
			
			writeWorkBook(workbook, fileNamePath);
		} catch (Exception e) {
			e.printStackTrace();
			//log.error(e.getMessage(), e);
		}
	}
	
	private static CellStyle createBorderedStyle(Workbook wb){
        CellStyle style = wb.createCellStyle();  
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());  
        style.setBorderBottom(CellStyle.BORDER_THIN);  
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());  
        style.setBorderLeft(CellStyle.BORDER_THIN);  
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());  
        style.setBorderTop(CellStyle.BORDER_THIN);  
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());  
        return style;  
    }
	
}
